﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using ChinaDynastyTours.Models;

namespace ChinaDynastyTours.Controls
{
    class FeedbackCtrl
    {
        #region 获取旅行社评价录入数据

        //public static int GetFeedbackPageNumbers(string tourgroupId)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT Max(PageNumber) FROM orgt_Feedback WHERE TourGroupID='{0}'", tourgroupId);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    object obj = db.ExecuteScalar(dbCommand);
        //    return obj == DBNull.Value ? 0 : int.Parse(obj.ToString());
        //}
        //public static DataTable GetFeedbackData(string tourgroupId,int pageNumber)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT * FROM orgt_Feedback WHERE TourGroupID='{0}' AND PageNumber={1} ORDER BY FeedbackID", tourgroupId, pageNumber);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 获取旅行社和宾馆的分组信息
        /// </summary>
        /// <param name="tourgroupId">团ID</param>
        /// <returns></returns>
        //public static DataTable GetFeedbackTravelAndHotelProviderIDGroup(string tourgroupId)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT ProviderID,HotelID FROM orgt_Feedback WHERE TourGroupID='{0}'GROUP BY ProviderID,HotelID", tourgroupId);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 根据旅行社ID和宾馆ID获取在团中有关它们的所有评价信息和平均值
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <param name="providerId"></param>
        /// <returns></returns>
//        public static DataTable GetFeedbackGroupAverageDataByTravelProviderID(string tourgroupId, string providerId,string hotelId)
//        {

//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;
//            string sql = string.Format(@"SELECT sum(Vehicle)*1.0/Count(Vehicle) as AverageVehicle,Sum(Restaurant)*1.0/Count(Restaurant) as AverageRestaurant,Sum(Park)*1.0/Count(Park) as AveragePark,Sum(Shop)*1.0/Count(Shop) as AverageShop,
//                                        Sum(Guide)*1.0/Count(Guide) as AverageGuide,Sum(Hotel)*1.0/Count(Hotel) as AverageHotel FROM orgt_Feedback WHERE TourGroupID='{0}' AND ProviderID={1} AND　hotelId={2}", tourgroupId, providerId,hotelId);
//            dbCommand = db.GetSqlStringCommand(sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0)
//                return ds.Tables[0];
//            else
//                return null;
//        } 

        #endregion

        #region 更新旅行社服务评价数据

//        internal static string CreateFeedbackData(FeedbackEntity entity)
//        {
//            Database db = DatabaseFactory.CreateDatabase();

//            string sql = string.Format(@"INSERT INTO orgt_Feedback ([TourgroupID], [TourgroupName], [ProviderID], [ProviderName], [Vehicle], [Restaurant], [Park], [Shop], [Guide],  [CreateByUserName], [CreateDateTime],[Hotel],[CityName],[HotelID],[HotelName],[PageNumber])
//                                         VALUES('{0}', '{1}',{2},'{3}',{4},{5},{6},{7},{8},'{9}','{10}',{11},'{12}',{13},'{14}',{15});Select SCOPE_IDENTITY();",
//                                          entity.TourgroupID,
//                                          entity.TourgroupName,
//                                          entity.ProviderID,
//                                          entity.ProviderName,
//                                          entity.Vehicle,
//                                          entity.Restaurant,
//                                          entity.Park,
//                                          entity.Shop,
//                                          entity.Guide,
//                                          entity.CreateByUserName,
//                                          entity.CreateDateTime,
//                                          entity.Hotel,
//                                          entity.CityName,
//                                          entity.HotelID,
//                                          entity.HotelName,
//                                          entity.PageNumber);

//            DbCommand dbCommand = db.GetSqlStringCommand(sql);

//            return db.ExecuteScalar(dbCommand).ToString();
//        }

//        internal static int UpdateFeedbackData(FeedbackEntity entity)
//        {
//            Database db = DatabaseFactory.CreateDatabase();
//            string sql = string.Format(@"UPDATE orgt_Feedback SET [TourgroupID]='{0}', [TourgroupName]='{1}', 
//                                                                  [Vehicle]={2}, [Restaurant]={3}, [Park]={4}, [Shop]={5}, [Guide]={6}, 
//                                                                  [CreateByUserName]='{7}', [CreateDateTime]='{8}',[Hotel]={9},[CityName]='{10}'  
//                                                                  WHERE [FeedbackID]={11}",
//                                                                  entity.TourgroupID,
//                                                                  entity.TourgroupName,
//                                                                  entity.Vehicle,
//                                                                  entity.Restaurant,
//                                                                  entity.Park,
//                                                                  entity.Shop,
//                                                                  entity.Guide,
//                                                                  entity.CreateByUserName,
//                                                                  entity.CreateDateTime,
//                                                                  entity.Hotel,
//                                                                  entity.CityName,
//                                                                  entity.FeedbackID);

//            DbCommand dbCommand = db.GetSqlStringCommand(sql);

//            return db.ExecuteNonQuery(dbCommand);
//        }

        //internal static void DeleteFeedbackData(string tourgroupId,int pageNumber)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    //删除指定页
        //    string sql = string.Format(@"DELETE FROM orgt_Feedback WHERE TourGroupID='{0}' AND PageNumber={1}", tourgroupId, pageNumber);
        //    db.ExecuteNonQuery(CommandType.Text, sql);
            
        //    //查找大于删除页的页号
        //    sql = string.Format("SELECT PageNumber FROM orgt_Feedback WHERE TourGroupID='{0}' AND PageNumber>{1} GROUP BY PageNumber ORDER BY PageNumber ASC", tourgroupId, pageNumber);
        //    DataSet ds = db.ExecuteDataSet(CommandType.Text,sql);
            
        //    //将后续页号提前
        //    if (ds.Tables.Count > 0)
        //    {
        //        foreach (DataRow dr in ds.Tables[0].Rows)
        //        {
        //            sql = string.Format(@"UPDATE orgt_Feedback SET PageNumber=PageNumber-1 WHERE  TourGroupID='{0}' AND PageNumber={1}", tourgroupId, dr["PageNumber"]);
        //            db.ExecuteNonQuery(CommandType.Text, sql);
        //        }
        //    }

        //}

        #endregion


        #region 获取旅行社服务统计数据

        //public static bool JudgeStatisticsExistForTourgroup(string tourgroupId)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT * FROM orgt_FeedbackStatistics WHERE TourGroupID='{0}'", tourgroupId);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);

        //    return (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count>0);
        //}
        //internal static DataTable GetFeedbackDataStatisticsForTourgroup(string tourgroupId, string fromDateTime, string toDateTime)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT * FROM orgt_FeedbackStatistics WHERE TourgroupID='{0}' AND CreateDateTime BETWEEN '{1}' AND '{2}'", tourgroupId, fromDateTime, toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 获取指定旅行社的反馈统计信息
        /// </summary>
        /// <param name="travelId"></param>
        /// <param name="fromDateTime"></param>
        /// <param name="toDateTime"></param>
        /// <returns></returns>
        //public static DataTable GetFeedbackStatisticsDataForTravel(string travelId,string fromDateTime,string toDateTime)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT * FROM orgt_FeedbackStatistics WHERE ProviderID='{0}' AND CreateDateTime BETWEEN '{1}' AND '{2}'", travelId, fromDateTime,toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;

        //}
        /// <summary>
        /// 获取指定宾馆的反馈统计信息
        /// </summary>
        /// <param name="hotelId"></param>
        /// <param name="fromDateTime"></param>
        /// <param name="toDateTime"></param>
        /// <returns></returns>
        //public static DataTable GetFeedbackStatisticsDataForHotel(string hotelId, string fromDateTime, string toDateTime)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT * FROM orgt_FeedbackStatistics WHERE HotelID='{0}' AND CreateDateTime BETWEEN '{1}' AND '{2}'", hotelId, fromDateTime, toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;

        //}
        /// <summary>
        /// 根据时间段，获取‘orgt_FeedbackStatistics’中多旅行社的分组信息
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        //public static DataTable GetStatisticsGroupForTravel(string formDateTime,string toDateTime)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT ProviderID FROM orgt_FeedbackStatistics WHERE CreateDateTime BETWEEN '{0}' AND '{1}' GROUP BY ProviderID", formDateTime, toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 根据时间段，获取‘orgt_FeedbackStatistics’中多宾馆的分组信息
        /// </summary>
        /// <param name="datetime"></param>
        /// <returns></returns>
        //public static DataTable GetStatisticsGroupForHotel(string formDateTime, string toDateTime)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format("SELECT HotelID FROM orgt_FeedbackStatistics WHERE CreateDateTime BETWEEN '{0}' AND '{1}' GROUP BY HotelID", formDateTime, toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //}
        /// <summary>
        /// 根据时间段和GetStatisticsGroup函数获取的旅行社ID，计算并返回‘orgt_FeedbackStatistics’中统计信息的再次求和后的平均值
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
//        public static DataTable GetStatisticsGroupAverageForTravel(string providerId,string fromDateTime,string toDateTime)
//        {

//            Database db = DatabaseFactory.CreateDatabase();

//            DbCommand dbCommand;
//            string sql = string.Format(@"SELECT Sum(Vehicle)/Count(Vehicle) as AverageVehicle,Sum(Restaurant)/Count(Restaurant) as AverageRestaurant,Sum(Park)/Count(Park) as AveragePark,Sum(Shop)/Count(Shop) as AverageShop,
//                                        Sum(Guide)/Count(Guide) as AverageGuide,Sum(Hotel)/Count(Hotel) as AverageHotel FROM orgt_FeedbackStatistics WHERE ProviderID={0} AND CreateDateTime BETWEEN '{1}' AND '{2}'", providerId, fromDateTime, toDateTime);
//            dbCommand = db.GetSqlStringCommand(sql);

//            DataSet ds = db.ExecuteDataSet(dbCommand);
//            if (ds.Tables.Count > 0)
//                return ds.Tables[0];
//            else
//                return null;
//        }

        /// <summary>
        /// 根据时间段和GetStatisticsGroup函数获取的宾馆ID，计算并返回‘orgt_FeedbackStatistics’中统计信息的再次求和后的平均值
        /// </summary>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        //public static DataTable GetStatisticsGroupAverageForHotel(string hotelId, string fromDateTime, string toDateTime)
        //{

        //    Database db = DatabaseFactory.CreateDatabase();

        //    DbCommand dbCommand;
        //    string sql = string.Format(@"SELECT Sum(Hotel)/Count(Hotel) as AverageHotel FROM orgt_FeedbackStatistics WHERE HotelID={0} AND CreateDateTime BETWEEN '{1}' AND '{2}'", hotelId, fromDateTime, toDateTime);
        //    dbCommand = db.GetSqlStringCommand(sql);

        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //} 
        #endregion

        #region 更新旅行社务统计数据

//        internal static int CreateFeedbackStatisticsData(List<FeedbackStatisticsEntity> Entities)
//        {
//            Database db = DatabaseFactory.CreateDatabase();
//            StringBuilder sql = new StringBuilder();
            
//            if (Entities.Count == 0) return -1;

//            foreach (var entity in Entities)
//            {
//                sql.AppendFormat(@"INSERT INTO orgt_FeedbackStatistics ([TourgroupID], [TourgroupName], [ProviderID], [ProviderName], [Vehicle], [Restaurant], [Park], [Shop], [Guide], [CreateDateTime],[Hotel],[HotelID],[HotelName])
//                                         VALUES('{0}', '{1}',{2},'{3}',{4},{5},{6},{7},{8},'{9}',{10},{11},'{12}');",
//                                              entity.TourgroupID,
//                                              entity.TourgroupName,
//                                              entity.ProviderID,
//                                              entity.ProviderName,
//                                              entity.Vehicle,
//                                              entity.Restaurant,
//                                              entity.Park,
//                                              entity.Shop,
//                                              entity.Guide,
//                                              entity.CreateDateTime,
//                                              entity.Hotel,
//                                              entity.HotelID,
//                                              entity.HotelName);
//            }

//            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());

//            return db.ExecuteNonQuery(dbCommand);
//        }
        /// <summary>
        /// 删除指定团的旅行社服务统计数据
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <returns></returns>
        //internal static int DeleteFeedbackStatisticsData(string tourgroupId)
        //{
        //    if (tourgroupId == "") return 0;

        //    Database db = DatabaseFactory.CreateDatabase();

        //    string sql = string.Format(@"DELETE FROM orgt_FeedbackStatistics WHERE TourgroupID='{0}'", tourgroupId);

        //    return db.ExecuteNonQuery(CommandType.Text, sql);
        //}

        #endregion
    }
}
